package admin.statisticsToExcel;

import java.io.File;

import javax.swing.GroupLayout.Alignment;

import jxl.CellView;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.struts2.ServletActionContext;

import admin.service.member.LeaderServiceAdmin;
import teacher.bean.Teacher;


public class ProjectToExcel {

	
	private static final WritableFont HEADER_FONT_STYLE = null;

	public static void projectExcel()
	{
        try {
            WritableWorkbook wwb = null; 
            String uploadPath = ServletActionContext.getServletContext()
					.getRealPath("/upload");
            
            File dir=new File(uploadPath);  
	       //如果这个目录不存在,则创建它  
	        if (!dir.exists()){  
	            dir.mkdir();  
	        } 
               // 创建可写入的Excel工作簿
              String fileName = uploadPath+"/"+"projectCount.xls";
               File file=new File(fileName);
               if (!file.exists()){
                   file.createNewFile();
               }
               //以fileName为文件名来创建一个Workbook
               wwb = Workbook.createWorkbook(file);
               // 创建工作表
               WritableSheet ws = wwb.createSheet("Test Shee 1", 0);  
               //合并单元格；
               ws.mergeCells(1, 1, 3, 1);
               ws.mergeCells(4, 1, 7, 1);
               ws.mergeCells(8, 1, 8, 2);
               ws.mergeCells(9, 1, 9, 2);
               ws.mergeCells(10, 1, 10, 2);
               ws.mergeCells(0, 1, 0, 2);
               ws.mergeCells(0, 0, 10,0);
               
               //合并单元格  生成标题  控制标题样式
               WritableFont bold = new WritableFont(WritableFont.ARIAL,12,WritableFont.BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
               WritableCellFormat titleFormate = new WritableCellFormat(bold);//生成一个单元格样式控制对象
               titleFormate.setAlignment(jxl.format.Alignment.CENTRE);//单元格中的内容水平方向居中
               titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//单元格的内容垂直方向居中
               Label title = new Label(0,0,"信息工程学院 控制科学与工程学科 科研项目统计",titleFormate);
               ws.setRowView(0, 600, false);//设置第一行的高度
               ws.addCell(title);
               
               //设置字体颜色
               WritableFont color = new WritableFont(WritableFont.ARIAL);//选择字体
               color.setColour(Colour.BLUE);//设置字体颜色为金黄色
               WritableCellFormat colorFormat0 = new WritableCellFormat(color);

               //设置单元格格式
               WritableCellFormat cellFormat = new WritableCellFormat();
               cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
               cellFormat.setAlignment(jxl.format.Alignment.CENTRE);
               //居中对齐
               WritableCellFormat cellFormat0 = new WritableCellFormat();
               cellFormat0.setAlignment(jxl.format.Alignment.CENTRE);
               //设置列宽 自适应
               CellView cellView = new CellView(); 
               cellView.setAutosize(true); //设置自动大小    
            /*   ws.setColumnView(0, cellView);//根据内容自动设置列宽    
               ws.setColumnView(1, cellView);//根据内容自动设置列宽    
               ws.setColumnView(2, cellView);//根据内容自动设置列宽    
               ws.setColumnView(3, cellView);//根据内容自动设置列宽    
               ws.setColumnView(4, cellView);//根据内容自动设置列宽    
               m_sheet.setColumnView(0, 10);   
               m_sheet.setColumnView(0, 10);                         
*/            ws.setColumnView(0, 20);
              ws.setColumnView(1, 15);
              ws.setColumnView(2, 18);
              ws.setColumnView(3, 18);
              ws.setColumnView(4, 18);
              ws.setColumnView(5, 15);
              ws.setColumnView(6, 15);
              ws.setColumnView(7, 15);
              ws.setColumnView(8, 15);
              ws.setColumnView(9, 18);
              ws.setColumnView(10, 18);
              ws.setColumnView(11, 18);
           
            
             




         

              
       //        cellFormat1.setAlignment(Alignment.CENTRE);
  // 垂直居中
              //要插入到的Excel表格的行号，默认从0开始
               Label aid= new Label(0, 1, "方向名称");//表示第
               Label aname= new Label(1, 1, "横向项目");//表示第
               Label aprojectName= new Label(4, 1, "纵向项目");
               
               Label alevel= new Label(1, 2, "100万以上项目数量");   
               Label apeople= new Label(2, 2, "50-100万项目数量");
               Label apeople1= new Label(3, 2, "50万以下项目总数");
               Label apeople10= new Label(4, 2, "国家级项目总数");
               Label apeople11= new Label(5, 2, "省部级项目总数");
               Label apeople112= new Label(6, 2, "厅局级项目总数");
               Label apeople113= new Label(7, 2, "校级项目总数");
      
               Label apeople12= new Label(8, 1, "项目总数");
               Label apeople2= new Label(9, 1, "经费（单位：万）");
               Label apeople3= new Label(10, 1, "到账经费（单位：万）");
         
              // ws.addCell(aid);
            //   ws.addCell(aname);
            //   ws.addCell(aprojectName);
               ws.addCell(alevel);
               ws.addCell(apeople);
               ws.addCell(apeople1);
               ws.addCell(apeople10);
               ws.addCell(apeople11);
               ws.addCell(apeople112);
               ws.addCell(apeople113);
          
               //将单元格内容添加到定义好格式的单元格内；
               aid.setCellFormat(cellFormat);           
               aname.setCellFormat(cellFormat0);
               aprojectName.setCellFormat(cellFormat0);
               apeople12.setCellFormat(cellFormat);
               apeople2.setCellFormat(cellFormat);
               apeople3.setCellFormat(cellFormat);
               ws.addCell(aid);
               ws.addCell(aname);
               ws.addCell(aprojectName);
               ws.addCell(apeople12);
               ws.addCell(apeople2);
               ws.addCell(apeople3);
              
               
               LeaderServiceAdmin ls=new LeaderServiceAdmin();
 
               for (int i = 0; i < 4; i++) 
               {  
            	  Teacher tea =new Teacher();
            	  tea.setDirection(i);
            	   Label aaid= new Label(1, i+3,ls.getCountOfProjectClass1(0, tea)+"");
                   Label aaname= new Label(2, i+3, ls.getCountOfProjectClass1(1, tea)+"");
                   Label aaprojectName= new Label(3, i+3,ls.getCountOfProjectClass1(2, tea)+"");
                   Label aaprojectName11= new Label(4, i+3,ls.getCountOfProjectClass0(0, tea)+"");
                   Label aaprojectName12= new Label(5, i+3,ls.getCountOfProjectClass0(1, tea)+"");
                   Label aaprojectName13= new Label(6, i+3,ls.getCountOfProjectClass0(2, tea)+"");
                   Label aaprojectName14= new Label(7, i+3,ls.getCountOfProjectClass0(3, tea)+"");
                   Label aalevel5= new Label(8, i+3, ls.getCountOfDirectProject(tea)+""); 
                   Label aalevel1= new Label(9, i+3, ls.getCountOfProjectFunds(tea)+""); 
                   Label aalevel2= new Label(10, i+3, ls.getCountOfProjectArriveFunds(tea)+""); 
                                 	              
                   ws.addCell(aaid);
                   ws.addCell(aaname);
                   ws.addCell(aaprojectName);
                   ws.addCell(aaprojectName11);
                   ws.addCell(aaprojectName12);
                   ws.addCell(aaprojectName13);
                   ws.addCell(aaprojectName14);
                 
                  ws.addCell(aalevel5); 
                 ws.addCell(aalevel1); 
                  ws.addCell(aalevel2); 
      
               }  
               Label id1= new Label(0, 3, "控制理论与控制工程 "+"");
               Label id2= new Label(0, 4,"机器人系统与智能装备 "+"");
               Label id3= new Label(0, 5, "模式识别与智能系统 "+"");
               Label id4= new Label(0, 6, "综合自动化与智能交通 "+"");
          
               id1.setCellFormat(colorFormat0);
               id2.setCellFormat(colorFormat0);
               id3.setCellFormat(colorFormat0);
               id4.setCellFormat(colorFormat0);
      
               ws.addCell(id1);
               ws.addCell(id2);
               ws.addCell(id3);
               ws.addCell(id4);
               
              //写进文档
               wwb.write();
              // 关闭Excel工作簿对象
               wwb.close();     
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } 
    }
}
